#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto

if [[ $1 == "" ]];then
   db=zx_dws
else
   db=$1
fi

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


-- 每年
SELECT
-- ①每年全国各个地区访问用户量、访问IP个数、访问Session个数
    year(create_date_time) as `年份` ,
    count(DISTINCT sid) as `每年访客id` ,
    count(DISTINCT ip) as `每年访客ip` ,
    COUNT (DISTINCT session_id) as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `year_seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `year_origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `year_msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `year_from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `quarter_area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time
UNION ALL


---------------------------------------每年每个地区的访客
---------------------------------------每年每个地区的访客
---------------------------------------每年每个地区的访客
---------------------------------------每年每个地区的访客

SELECT
    year(create_date_time)as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    area,
    count(DISTINCT sid) as `每年每个地区访客id` ,
    count(DISTINCT ip) as `每年每个地区访客ip`,
    COUNT (DISTINCT session_id) as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,

-- 每季度
     null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `quarter_area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `quarter_seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `quarter_origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `quarter_msg_count`,
    null as `quarter_msg_count_id` ,


    null as `quarter_from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,area

UNION ALL
---------------------------------------每年每个
---------------------------------------每年每个
SELECT
    year(create_date_time)as `年份` ,

    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    seo_source,
    count(DISTINCT sid) `每年每个搜索来源id` ,
    count(DISTINCT ip)`每年每个搜索来源ip` ,
    COUNT (DISTINCT session_id)`每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,

-- 每季度
     null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,seo_source

UNION ALL

SELECT
    year(create_date_time)as `年份` ,

    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    origin_channel,
    count(DISTINCT sid)as `每年每个来源渠道id`,
    count(DISTINCT ip) as `每年每个来源渠道ip`,
    COUNT (DISTINCT session_id) as `每年每个来源渠道session_id`,


    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,

-- 每季度
     null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,origin_channel
UNION ALL

SELECT
    year(create_date_time)as `年份` ,


    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    msg_count,
    count(DISTINCT sid) as `每年每个客户发送消息渠道id` ,
    count(DISTINCT ip) as `每年每个客户发送消息渠道ip` ,
    COUNT (DISTINCT session_id) as `每年每个客户发送消息渠道session_id` ,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,

-- 每季度
     null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,msg_count
UNION ALL

SELECT
    year(create_date_time) as `年份`,

    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    from_url,
    count(DISTINCT sid)as `每年每个会话来源id`  ,
    count(DISTINCT ip) as `每年每个会话来源ip` ,
    COUNT (DISTINCT session_id) as `每年每个会话来源session_id` ,

-- 每季度
     null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17 `dw`
LEFT JOIN dwd_visit_consult.web_chat_text_ems_2019_07 `dt`
on dw.id=dt.id
GROUP BY create_date_time,from_url


------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-------每季度

SELECT
--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    quarter(create_date_time) as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
     null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time

UNION ALL


SELECT
--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
       null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,area

UNION ALL


SELECT
--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
     null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,seo_source

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
      null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,origin_channel

UNION ALL

SELECT


--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
     null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,msg_count

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17 `dw`
LEFT JOIN dwd_visit_consult.web_chat_text_ems_2019_07 `dt`
on dw.id=dt.id
GROUP BY create_date_time,from_url;




-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
--       每月份
-------------------------------------------------------------------------
-------------------------------------------------------------------------

SELECT
--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    month(create_date_time)  as `月份`,
    count(DISTINCT sid) as `每月访客id` ,
    count(DISTINCT ip) as `每月访客ip` ,
    COUNT (DISTINCT session_id) as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time

UNION ALL


SELECT
--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    month(create_date_time) as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    area,
    count(DISTINCT sid) as `每月每个地区访客id`,
    count(DISTINCT ip) as `每月每个地区访客ip`,
    COUNT (DISTINCT session_id) as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,area

UNION ALL


SELECT
--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    month(create_date_time) as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    seo_source,
    count(DISTINCT sid) as `每月每个搜索来源id` ,
    count(DISTINCT ip) as `每月每个搜索来源ip`,
    COUNT (DISTINCT session_id) as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,seo_source

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    month(create_date_time) as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    origin_channel,
    count(DISTINCT sid) as `每月每个来源渠道id` ,
    count(DISTINCT ip) as `每月每个来源渠道ip`,
    COUNT(DISTINCT session_id) as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,origin_channel

UNION ALL

SELECT


--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    month(create_date_time) as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    msg_count,
    count(DISTINCT sid) as `每月每个客户发送消息渠道id` ,
    count(DISTINCT ip) as `每月每个客户发送消息渠道ip`,
    COUNT (DISTINCT session_id) as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,msg_count

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    month(create_date_time) as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    from_url,
    count(DISTINCT sid) as `每月每个会话来源id` ,
    count(DISTINCT ip) as `每月每个会话来源ip`,
    COUNT (DISTINCT session_id) as `每月每个会话来源session_id`,
--每天
    null as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17 `dw`
LEFT JOIN dwd_visit_consult.web_chat_text_ems_2019_07 `dt`
on dw.id=dt.id
GROUP BY create_date_time,from_url;

---------------------------------------------------------------
---------------------------------------
------------------------------------------
------------------------------------------







-- 每天
SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    to_date(create_date_time) as `天`,
    count(DISTINCT sid) as `每天访客id` ,
    count(DISTINCT ip) as `每天访客ip` ,
    COUNT (DISTINCT session_id) as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    to_date(create_date_time) as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    area,
    count(DISTINCT sid) as `每天每个地区访客id` ,
    count(DISTINCT ip) as `每天每个地区访客ip`,
    COUNT (DISTINCT session_id) as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,area

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    to_date(create_date_time) as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    seo_source,
    count(DISTINCT sid) as `每天每个搜索来源id` ,
    count(DISTINCT ip) as `每天每个搜索来源ip`,
    COUNT (DISTINCT session_id) as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,seo_source

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    to_date(create_date_time) as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    origin_channel,
    count(DISTINCT sid) as `每天每个来源渠道id` ,
    count(DISTINCT ip) as `每天每个来源渠道ip`,
    COUNT (DISTINCT session_id) as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,origin_channel

UNION ALL

SELECT


--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    to_date(create_date_time) as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    msg_count,
    count(DISTINCT sid) as `每天每个客户发送消息渠道id` ,
    count(DISTINCT ip) as `每天每个客户发送消息渠道ip`,
    COUNT (DISTINCT session_id) as `每天每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每天每个会话来源id` ,
    null as `每天每个会话来源ip`,
    null as `每天每个会话来源session_id`



FROM dwd_visit_consult.web_chat_ems_2019_17
GROUP BY create_date_time,area

UNION ALL

SELECT

--按年
    NULL as `年份` ,
    null as `每年访客id` ,
    null as `每年访客ip` ,
    null as `每年访客session_id`,

    null as `year_area`,
    null as `每年每个地区访客id` ,
    null as `每年每个地区访客ip`,
    null as `每年每个地区访客session_id`,

    null as `seo_source`,
    null as `每年每个搜索来源id` ,
    null as `每年每个搜索来源ip`,
    null as `每年每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每年每个来源渠道id` ,
    null as `每年每个来源渠道ip`,
    null as `每年每个来源渠道session_id`,

    null as `msg_count`,
    null as `每年每个客户发送消息渠道id` ,
    null as `每年每个客户发送消息渠道ip`,
    null as `每年每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每年每个会话来源id` ,
    null as `每年每个会话来源ip`,
    null as `每年每个会话来源session_id`,


-- 每季度
    null as `季度`,
    null as `每季度访客id` ,
    null as `每季度访客ip` ,
    null as `每季度访客session_id`,

    null as `area`,
    null as `每季度每个地区访客id` ,
    null as `每季度每个地区访客ip`,
    null as `每季度每个地区访客session_id`,

    null as `seo_source`,
    null as `每季度每个搜索来源id` ,
    null as `每季度每个搜索来源ip`,
    null as `每季度每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每季度每个来源渠道id` ,
    null as `每季度每个来源渠道ip`,
    null as `每季度每个来源渠道session_id`,

    null as `msg_count`,
    null as `quarter_msg_count_id` ,
    null as `每季度每个客户发送消息渠道ip`,
    null as `每季度每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每季度每个会话来源id` ,
    null as `每季度每个会话来源ip`,
    null as `每季度每个会话来源session_id`,

--每月
    null as `月份`,
    null as `每月访客id` ,
    null as `每月访客ip` ,
    null as `每月访客session_id`,

    null as `area`,
    null as `每月每个地区访客id` ,
    null as `每月每个地区访客ip`,
    null as `每月每个地区访客session_id`,

    null as `seo_source`,
    null as `每月每个搜索来源id` ,
    null as `每月每个搜索来源ip`,
    null as `每月每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每月每个来源渠道id` ,
    null as `每月每个来源渠道ip`,
    null as `每月每个来源渠道session_id`,

    null as `msg_count`,
    null as `每月每个客户发送消息渠道id` ,
    null as `每月每个客户发送消息渠道ip`,
    null as `每月每个客户发送消息渠道session_id`,

    null as `from_url`,
    null as `每月每个会话来源id` ,
    null as `每月每个会话来源ip`,
    null as `每月每个会话来源session_id`,
--每天
    to_date(create_date_time) as `天`,
    null as `每天访客id` ,
    null as `每天访客ip` ,
    null as `每天访客session_id`,

    null as `area`,
    null as `每天每个地区访客id` ,
    null as `每天每个地区访客ip`,
    null as `每天每个地区访客session_id`,

    null as `seo_source`,
    null as `每天每个搜索来源id` ,
    null as `每天每个搜索来源ip`,
    null as `每天每个搜索来源session_id`,

    NULL as `origin_channel`,
    null as `每天每个来源渠道id` ,
    null as `每天每个来源渠道ip`,
    null as `每天每个来源渠道session_id`,

    null as `msg_count`,
    null as `每天每个客户发送消息渠道id` ,
    null as `每天每个客户发送消息渠道ip`,
    null as `每天每个客户发送消息渠道session_id`,

    from_url,
    count(DISTINCT sid) as `每天每个会话来源id` ,
    count(DISTINCT ip) as `每天每个会话来源ip`,
    COUNT (DISTINCT session_id) as `每天每个会话来源session_id`

FROM dwd_visit_consult.web_chat_ems_2019_17 `dw`
LEFT JOIN dwd_visit_consult.web_chat_text_ems_2019_07 `dt`
on dw.id=dt.id
GROUP BY create_date_time,from_url;

"